<?php
/**
 * Class Common created by cephuo.
 *
 * Date: 7/28/11
 *
 * Time: 11:32 AM
 *
 * This class is mainly intended to be used a superclass(parent class) for other classes. Each subclass(child class, heir class)
 * being associated with a certain important DB table. The table must have a primary key field, whose name can be specified
 * in the $idField at inheriting. A <b>minimal</b> subclass must <b>at least</b> overwrite the $dbFields and __construct
 * @see Db_Cms_Pages for an exemple
 * At the same time, this class provides a number of useful static DB function that can be used without inheriting.
 * This is a cornerstone class that provides the Model(M) functionality for our implementation of MVC
 * @see http://en.wikipedia.org/wiki/Model%E2%80%93view%E2%80%93controller
 * @property int $id The default primary key
 * @property int $deleted A special field used for 'abstract deletion', that is, when the entry is not actually(physically) deleted from the table, but instead has `deleted` set to 1, this allowing for undoing the deletion
 */

class Common extends Engine {
	/**
	 * Database table name where the object will be stored.
	 * @static
	 */
	protected static $tableName;

	/**
	 * Database table fields, usually are the same as class properties, please note that the fields specified in this array must also be declared as PUBLIC NON-STATIC class fields
	 * @static
	 */
	protected static $dbFields;

	/**
	 * The name of the auto-incremented field from the table
	 */
	protected static $idField = 'id';

    public $positionCommonFields = array();

	/**
	 * @var string The name of a table field used for positioning an element, if available, applicable and necessary
	 */
	public static $positionField;

	public $objectArray;

	/**
	 * The constructor can fetch a DB entry right at the instantiation. Note that both arguments are optional.
	 * If no arguments are supplied, it will instantiate an empty object, no DB query being made, which then can be
	 * populated with data and saved to the DB using self::save()
	 *
	 * If the constructor is given only one argument, it will query the DB for an entry whose <b>primary key</b>
	 * matches the argument. Note that if such entry does not exist, no error will be triggered or exception thrown, thus
	 * that is the programmer's responsibility to check if the object has been properly instantiated, if ($object->id) { //...
	 *
	 * If a second argument is passed, the constructor will take it as an SQL table field name, matching that field's value
	 * against the first argument
	 * @see \Common::save()
	 * @uses \Common::findById($id)
	 * @uses \Common::findByField($fieldName,$value,$orderBy)
	 * @param mixed $value The value of the field to match in the db. If not supplied, no request will be executed and an empty object will be instantiated
	 * @param string $fieldName The name of the field to match in the DB. If not specified, the $value will be matched against the table's primary key, which can be changed in self::$tableName
	 */
	public function __construct($value=NULL, $fieldName=NULL) {
		if ($value != NULL) {
			if ($fieldName != NULL) {
				$this->objectArray = self::findByField($fieldName, $value);
			} else {
				$this->objectArray = self::findById($value);
			}
			if ($this->objectArray != false) {
				$this->{static::$idField} = $this->objectArray->{static::$idField};
			}
		}
    }

	/**
	 * Queries for all entries in a table. This function cannot be used without inheriting, as it relies on the self::$tableName
	 * for the name of the table to work with.
	 *
	 * @static
	 * @uses Common::find(...)
	 * @see Common::$tableName
	 * @see Common::findAllArray($fields,$orderBy)
	 * @param string|array $fields A list of fields to be selected. Can be a string of the form "id, p_name" or an array like array('id', 'p_name')
	 * @param bool|string $orderBy Optional. The name of the field to be used for sorting. If skipped, the result will be unsorted. You can use a string to specify the direction of sorting, i.e. "pr_name ASC"
	 * @return array|boolean An array of objects on success and FALSE on failure
	 */
	public static function findAll($fields = '*', $orderBy = false) {
		if (!empty($orderBy)) {
			$orderBy = " ORDER BY ".$orderBy;
		} else {
			$orderBy = "";
		}
		if (is_array($fields)) {
			if (count($fields) > 0) {
				$fields = '`'.implode("`, `",$fields).'`';
			} else {
				$fields = '*';
			}
		}
		return self::find("SELECT ".$fields." FROM ?f WHERE `deleted`=0 ".$orderBy, static::$tableName);
	}

	/**
	 * Queries for all entries in a table. This function cannot be used without inheriting, as it relies on the self::$tableName
	 * for the name of the table to work with.
	 *
	 * @static
	 * @uses Common::findArray(...)
	 * @see Common::$tableName
	 * @see Common::findAll($fields,$orderBy)
	 * @param string|array $fields A list of fields to be selected. Can be a string of the form "id, p_name" or an array like array('id', 'p_name')
	 * @param bool|string $orderBy Optional. The name of the field to be used for sorting. If skipped, the result will be unsorted. You can use a string to specify the direction of sorting, i.e. "pr_name ASC"
	 * @return array|boolean An array of assocs on success and FALSE on failure
	 */

	public static function findAllArray($fields = '*', $orderBy = false) {
		if (!empty($orderBy)) {
			$orderBy = " ORDER BY ".$orderBy;
		} else {
			$orderBy = "";
		}
		if (is_array($fields)) {
			if (count($fields) > 0) {
				$fields = '`'.implode("`, `",$fields).'`';
			} else {
				$fields = '*';
			}
		}
		return self::findArray("SELECT ".$fields." FROM ?f WHERE `deleted`=0 ".$orderBy, static::$tableName);
	}

	/**
	 * Find one row as object by primary key ($idField). This function will work only in a subclass.
	 * 
	 * @static
	 * @see Common::findArrayById($id)
	 * @uses Common::find(...)
	 * @uses Common::$tableName
	 * @uses Common:$idField
	 * @param int $id Id to find
	 * @return bool|object Object on success or FALSE on failure
	 */
	public static function findById($id = 0) {
		$resultArray = self::find("SELECT * FROM ?f WHERE ?f=?d AND `deleted`=0 LIMIT 1", static::$tableName, static::$idField, $id);
		return !empty($resultArray) ? array_shift($resultArray) : false;
	}

	/**
	 * Find one row as array by primary key ($idField). This function will work only in a subclass.
	 * 
	 * @static
	 * @see Common::findById($id)
	 * @uses MySQL::selectRow(...)
	 * @uses Common::$tableName
	 * @uses Common:$idField
	 * @param int $id Id to find
	 * @return bool|array Array on success and FALSE on failure
	 */
	public static function findArrayById($id = 0) {
		return self::$DB->selectRow("SELECT * FROM ?f WHERE ?f=?d AND `deleted`=0 LIMIT 1", static::$tableName, static::$idField, $id);
	}

	/**
	 * Find ONLY ONE object by field name and field value. This function will work only in a subclass.
	 *
	 * @static
	 * @see Common::findAllByField($field,$value,$orderBy)
	 * @see Common:findByFields($fieldValue,$condition,$orderBy)
	 * @uses Common::find(...)
	 * @uses Common::$tableName
	 * @param string $field The name of the field
	 * @param mixed $value The value to match
	 * @param bool|string $orderBy Optional. The name of the field to be used for sorting. If skipped, the result will be unsorted. You can use a string to specify the direction of sorting, i.e. "pr_name ASC"
	 * @return mixed Object on success and FALSE on failure
	 */
	public static function findByField($field, $value, $orderBy = false) {
		if (!empty($orderBy)) {
			$orderBy = " ORDER BY ".$orderBy;
		} else {
			$orderBy = "";
		}
		$resultArray = self::find("SELECT * FROM ?f WHERE ?f=? AND `deleted`=0 {$orderBy} LIMIT 1", static::$tableName, $field, $value);
		return !empty($resultArray) ? array_shift($resultArray) : false;
	}

	/**
	 * Find an array of objects by field name and field value. This function will work only in a subclass.
	 *
	 * @static
	 * @see Common:findByField($field,$value,$orderBy)
	 * @see Common:findByFields($fieldValue,$condition,$orderBy)
	 * @uses Common::find(...)
	 * @uses Common::$tableName
	 * @param string $field Field name
	 * @param mixed $value Field value
	 * @param bool|string $orderBy Optional. The name of the field to be used for sorting. If skipped, the result will be unsorted. You can use a string to specify the direction of sorting, i.e. "pr_name ASC"
	 * @return mixed Object on success and FALSE on failure
	 */
	public static function findAllByField($field, $value, $orderBy = false) {
		if (!empty($orderBy)) {
			$orderBy = " ORDER BY ".$orderBy;
		} else {
			$orderBy = "";
		}
		$resultArray = self::find("SELECT * FROM ?f WHERE ?f=? AND `deleted`=0 {$orderBy}", static::$tableName, $field, $value);
		return !empty($resultArray) ? $resultArray : false;
	}

	/**
	 * Counts all the entries in the table that match the condition
	 * @see Common::count()
	 * @uses Db_Page::selectCell(...)
	 * @uses self::$tableName
	 * @param string $field The name of the field
	 * @param mixed $value The value of the field to look for
	 * @return array|bool
	 */
	public static function countAllByField($field, $value) {
		return self::$DB->selectCell("SELECT COUNT(*) FROM ?f WHERE ?f=? AND `deleted`=0 ", static::$tableName, $field, $value);
	}

	/**
	 * Find a single row by multiple conditions.
	 * Returns an object with found row or false if no match was found.
	 *
	 * @static
	 * @see Common:findByField($field,$value,$orderBy)
	 * @see Common::findAllByField($field,$value,$orderBy)
	 * @uses Common::find(...)
	 * @uses Common::$tableName
	 * @param array $fieldValue must have this format: array($fieldName => $fieldValue [, $fieldName => $fieldValue] ...)
	 * @param string $condition An SQL condition, like "AND", "AND NOT", "OR" etc.
	 * @param bool|string $orderBy Optional. The name of the field to be used for sorting. If skipped, the result will be unsorted. You can use a string to specify the direction of sorting, i.e. "pr_name ASC"
	 * @return self object on success and FALSE on failure
	 */
	public static function findByFields($fieldValue = array(), $condition = 'AND', $orderBy=false) {
		if (!empty($orderBy)) {
			$orderBy = " ORDER BY ".$orderBy;
		} else {
			$orderBy = "";
		}
		$queryPart = "";
		$arraySize = count($fieldValue);
		$i=0;

		foreach ((array)$fieldValue as $key => $value) {
			$i++;
			$queryPart .= '`'.$key.'`='.self::$DB->escapeValue($value);
			if ($i < $arraySize) {
				$queryPart .= ' '.$condition.' ';
			}
		}
		$queryPart .= " AND deleted = 0 ";
		$resultArray = self::find("SELECT * FROM ?f WHERE ".$queryPart.$orderBy." LIMIT 1", static::$tableName);
		return !empty($resultArray) ? array_shift($resultArray) : false;
	}

	/**
	 * Fetch result by arbitrary sql statement. Can have an arbitrary number of arguments.
	 * The first parameter should always be the SQL query itself and the next parameters - values for placeholders.
	 * This function can be called statically from the base class Common::find(...)
	 *
	 * @static
	 * @see Common::findArray(...)
	 * @uses MySQL::qArgs($args,$storeResult)
	 * @return array|bool Array of objects on success or false if no rows were found
	 */
	public static function find() {
		$args = func_get_args();
		$dbResult = self::$DB->qArgs($args, true);
		$resultArray = array();

		if (isset($dbResult->num_rows) && $dbResult->num_rows > 0) {
			while ($row = $dbResult->fetch_object(get_called_class())) {
				$resultArray[] = $row;
			}
			$dbResult->close();
			return $resultArray;
		}
		return false;
	}

	/**
	 * Fetch result by arbitrary sql statement. Can have an arbitrary number of arguments.
	 * The first parameter should always be the SQL query itself and the next parameters - values for placeholders
	 * This function can be called statically from the base class Common::findArray(...)
	 *
	 * @static
	 * @return array|bool 2-dimensional array on success or false if no rows were found
	 */
	public static function findArray() {
		$args = func_get_args();
		$dbResult = self::$DB->qArgs($args, true);
		$resultArray = array();

		if ($dbResult->num_rows > 0) {
			while ($row = $dbResult->fetch_assoc()) {
				$resultArray[] = $row;
			}
			$dbResult->close();
			return $resultArray;
		}
		return false;
	}

	/**
	 * Generate an array with keys as current object Id and the value as object variable specified.
	 * This function will work only in a subclass.
	 *
	 * @static
	 * @uses MySQL::q($args,$storeResult)
	 * @uses Common::$tableName
	 * @param mixed $valueCol The name of a DB table field, which values will serve as result's keys
	 * @param string|bool $keyCol The name of a DB table field, which values will serve as result's values
	 * @param bool|string $orderBy Optional. The name of the field to be used for sorting. If skipped, the result will be unsorted. You can use a string to specify the direction of sorting, i.e. "pr_name ASC"
	 * @return array An one-dimension, associative array, where the values of the field specified by the first parameter will serve as keys, and the values of the field specified by the second parameter as respective keys' values. Returns an empty array if nothing found
	 */
	static function getElementsForSelect($valueCol, $keyCol = false, $orderBy = false) {
		if (!$keyCol) {
			$keyCol = self::$idField;
		}
        if (!empty($orderBy)) {
            $orderBy = " ORDER BY ".$orderBy;
        } else {
            $orderBy = "";
        }
		$result = self::$DB->q("SELECT ?f,?f FROM ?f WHERE `deleted`=0 {$orderBy};", $keyCol, $valueCol, static::$tableName);
		$elementsArr = array();
        while ($element = $result->fetch_assoc()) {
            $elementsArr[$element[$keyCol]] = $element[$valueCol];
        }
		return $elementsArr;
	}

	/**
	 * Count all records from class table
	 *
	 * @static
	 * @see Common::countAllByField($field,$value)
	 * @uses MySQL::selectCell(...)
	 * @uses Common::$tableName
	 * @return integer
	 */
	public static function countAll() {
		return self::$DB->selectCell("SELECT COUNT(*) FROM ?f WHERE `deleted` = 0;", static::$tableName);
	}

	/**
	 * Returns all the current subclass' properties that match the corresponding table's fields. That is, are declared
	 * in Common::$dbFields AND are declared as subclass' properties
	 *
	 * @see Common::$dbFields
	 * @used-by Common::sanitizedAttributes()
	 * @static
	 * @return array An array where the field's name is the key and the field's value is the key's value
	 */
	protected function attributes() {
		$attributes = array();
		foreach(static::$dbFields as $field) {
			if(property_exists($this, $field)) {
				$attributes[$field] = $this->$field;
			}
		}
		return $attributes;
	}

	/**
	 * Sanitize (clean) attributes before submitting, i.e. escape field values
	 * Note: does not alter the actual value of each attribute
	 *
	 * @static
	 * @uses Common::attributes()
	 * @return array Array of clean attributes
	 */
	protected function sanitizedAttributes() {
		$cleanAttributes = array();
		foreach($this->attributes() as $key => $value){
			if (isset($value)) {
				$key = '`'.$key.'`';
				$cleanAttributes[$key] = self::$DB->escapeValue($value);
			}
		}
		return $cleanAttributes;
	}

	/**
	 * Save to Database. This function checks if the primary key field has a valid(not null, not false, non-zero)  value,
	 * and if it does, the function will update the existing entry with the values from subclass' fields. Otherwise, it will
	 * run an INSERT query
	 * Note: a new record won't have an id yet
	 * Each inherited class will save data in its own table
	 *
	 * @uses Common::$idField
	 * @uses Common::update()
	 * @uses Common::create($duplicateUpdate,$sumOnUpdate)
	 * @param bool $duplicateUpdate
	 * @param bool $sumOnUpdate
	 * @return bool
	 */
	public function save($duplicateUpdate = false, $sumOnUpdate = false) {
		return (int)$this->{static::$idField} ? $this->update() : $this->create($duplicateUpdate, $sumOnUpdate);
	}

	/**
	 * Insert a new record to the database
	 *
	 * @param bool $duplicateUpdate Suppose the table has a unique field or combination of fields, and the object you
	 * are trying to save does not respect this uniqueness. If this parameter is set to true, the existing field will be
	 * overwritten by the one being saved, otherwise, the saving will fail.
	 * @param bool $sumOnUpdate Suppose the table has a unique field or combination of fields, and the object you
	 * are trying to save does not respect this uniqueness. If this and the previous parameters are both set to true,
	 * the conflicting values of the current object will be accumulated(that is, added to) into the existing field.
	 * <b>Note</b>This parameter has no effect(since it does not make sense) if the first parameter is false
	 * @uses Common:commit()
	 * @uses MySQL::q(...)
	 * @return bool TRUE on success and FALSE on failure
	 */
	public function create($duplicateUpdate = false, $sumOnUpdate = false) {
		if ($duplicateUpdate && property_exists($this, 'deleted')) {
			$this->deleted=0;
		}
		$attributes = $this->sanitizedAttributes();
		$tmpSql = '';

		foreach($this->checkExistColumns(false) as $key => $value) {
			if(!empty($value)) {
				$attributes[$key] = MySQL::escapeValue($value);
			}
		}

		if ($duplicateUpdate) {
			$attributePairs = array();
			foreach($attributes as $key => $value) {
				if ($value == "''") {
					continue;
				}
				if ($sumOnUpdate) {
					$attributePairs[] = $key."=(".$key.'+'.$value.")";
				} else {
					$attributePairs[] = $key."=".$value;
				}
			}

			foreach($this->checkExistColumns() as $key => $value) {
					$attributePairs[] = $key . "=" . MySQL::escapeValue($value);
			}
			// We add here `id` = LAST_INSERT_ID(`id`) to make self::$DB->lastId() work correctly
			// http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html
			$tmpSql = " ON DUPLICATE KEY UPDATE `id` = LAST_INSERT_ID(`id`), " . implode(", ", $attributePairs) . " ";
		}

		$sql = "
			INSERT
			INTO	".static::$tableName."
					(" . join(", ", array_keys($attributes)) . ")
			VALUES	(" . join(", ", array_values($attributes)) . ")
			".$tmpSql;

		$this->lockTable();
		if(self::$DB->q($sql)) {
			$this->{static::$idField} = self::$DB->lastId();
			$this->commit();
			return true;
		} else {
			$this->commit();
			return false;
		}
	}

	/**
	 * Update a record from database
	 *
	 * @uses MySQL::q(...)
	 * @return boolean TRUE(at least one row was modified) on success and FALSE on failure(no rows matched the condition)
	 */
	public function update() {

		$attributes = $this->sanitizedAttributes();
		$extendAttributes = $this->checkExistColumns();
		$attributePairs = array();

		foreach($attributes as $key => $value) {
			$attributePairs[] = $key."=".$value;
		}
		foreach($extendAttributes as $key => $value) {
			$attributePairs[] = $key . "=" . MySQL::escapeValue($value);
		}
		$sql = "UPDATE	" . static::$tableName . "
				SET		" . implode(", ", $attributePairs) . "
				WHERE	`" . static::$idField . "`=" . (int)$this->{static::$idField} . ";";

		self::$DB->q($sql);
		if (self::$DB->mysqlModifiedRows() > 0) {
			return true;
		}

		return false;
	}

	/**
	 * This function checks if the table has one of the abstract delete related columns(`modified_date`, `add_user`,
	 * `modified_user` and `deleted`) and returns an array of the values that need to be updated.
	 *
	 * @uses Common::$tableName
	 * @uses MySQL::q(...)
	 * @used-by Common::abstractDeleteByField($field,$value,$limit=0)
	 * @used-by Common::abstractDelete($id = 0)
	 * @used-by Common::update()
	 * @used-by Common::create($duplicateUpdate,$sum)
	 * @param bool $onUpdate Whether this function is called when updating an existing entry or creating a new one
	 * @return array An associative array whose keys are the abstract delete related columns that exist in the table
	 * and values are the respective entry values that need to be updated
	 */
	public static function checkExistColumns($onUpdate = true) {
		$sql = "SELECT DISTINCT column_name
				FROM INFORMATION_SCHEMA.COLUMNS
				WHERE TABLE_NAME = ? AND (COLUMN_NAME = 'modified_date' OR COLUMN_NAME = 'add_user' OR COLUMN_NAME = 'modified_user' OR COLUMN_NAME = 'deleted') ;";
		$resultColumns = self::$DB->q($sql, static::$tableName);
		$columns = array();

		while($data = $resultColumns->fetch_object()) {
			if($data->column_name == 'modified_date' && $onUpdate == true) {
				$columns[$data->column_name] = Utils::formatDateDb(time(), true);
			} elseif($data->column_name == 'add_user' && $onUpdate == false) {
				$columns[$data->column_name] =  self::$USER['u_id'];
			} elseif($data->column_name == 'modified_user' && $onUpdate == true) {
				$columns[$data->column_name] = self::$USER['u_id'];
			} elseif($data->column_name == 'deleted' && $onUpdate == true) {
				$columns[$data->column_name] = 0;
			}
		}
		return $columns;
	}

	/**
	 * Delete the current record from the database.
	 * @uses Common::$idField
	 * @return boolean TRUE on success(exactly one row was deleted) and FALSE on failure(no rows matched the condition
	 * or more than one entries were deleted(which usually means you do not have a primary key in your table)
	 */
	public function delete() {
		$sql = "DELETE FROM ?f WHERE ?f=?i LIMIT 1;";
		self::$DB->q($sql, static::$tableName, static::$idField, $this->{static::$idField});
		return (self::$DB->affectedRows() == 1);
	}

	/**
	 * Delete a record by field name and value
	 *
	 * @uses Common::$tableName
	 * @param string $field Field name
	 * @param string $value Field value
	 * @param integer $limit Optional. Limit of deleted records
	 * @return boolean TRUE on success(at least one row was deleted) AND FALSE on failure(no rows matched the condition)
	 */
	static function deleteByField($field, $value, $limit=1) {
		$sql = "DELETE FROM ?f WHERE ?f = ? ";
		if ($limit > 0) {
			$sql .= " LIMIT ".(int)$limit;
		}
		self::$DB->q($sql, static::$tableName, $field, $value);
		return (self::$DB->affectedRows() > 0);
	}

	/**
	 * Physically(irreversibly) deletes all the rows in a table whose ids are NOT contained in an array of
	 * ids passed as argument. Also allows the habitual `field` = 'value' matching.
	 * This function takes an associative array as argument. The keys stand for field names, whilst the values represent
	 * the values these fields are matched against. If such a value is an array, the function will delete the rows that
	 * are NOT in the specified array, Otherwise, if the value is not an array, it will delete the rows whose fields MATCH the specified value
	 * @static
	 * @see Common::abstractDeleteByFieldsNotInArray($array)
	 * @param array $array An array of the form array(
	 *		'field1' => 123,
	 * 		'field2' => array(1,2,3)
	 * )
	 * In this example, the query will delete all the rows where the value of `field1` EQUALS 123 AND the value of field2
	 * is NOT contained in the array [1, 2, 3]
	 * @return bool This function returns true if at least one row matched the condition and was deleted. It returns FALSE otherwise
	 */
	static function deleteByFieldsNotInArray($array) {
		if (is_array($array) && count($array) > 0) {
			$whereNotIn = array();
			foreach($array as $fieldName => $value) {
				if(is_array($value) && !empty($fieldName)) {
					$whereNotIn[] =  $fieldName . ' NOT IN (' . implode(',',array_map(array('MySQL', 'escapeValue'), $value)).')';
				} else {
					$whereNotIn[] .= $fieldName . ' = ' .  MySQL::escapeValue($value);
				}
			}
			$where  = implode(' AND ', $whereNotIn);
			$sql = "DELETE FROM ?f WHERE {$where} AND deleted = 0";

			self::$DB->q($sql, static::$tableName);
		}
		return (self::$DB->affectedRows() > 0);
	}

	/**
	 * Abstractly deletes(that is, sets `deleted` = 1), all the rows in a table whose ids are NOT contained in an array of
	 * ids passed as argument. Also allows the habitual `field` = 'value' matching.
	 * This function takes an associative array as argument. The keys stand for field names, whilst the values represent
	 * the values these fields are matched against. If such a value is an array, the function will delete the rows that
	 * are NOT in the specified array, Otherwise, if the value is not an array, it will delete the rows whose fields MATCH the specified value
	 * @static
	 * @see Common::deleteByFieldsNotInArray($array)
	 * @param array $array An array of the form array(
	 *		'field1' => 123,
	 * 		'field2' => array(1,2,3)
	 * )
	 * In this example, the query will delete all the rows where the value of `field1` EQUALS 123 AND the value of field2
	 * is NOT contained in the array [1, 2, 3]
	 * @return bool This function returns true if at least one row matched the condition and was deleted. It returns FALSE otherwise
	 */
	static function abstractDeleteByFieldsNotInArray($array) {
		if (is_array($array) && count($array) > 0) {
			$whereNotIn = array();
			foreach($array as $fieldName => $value) {
				if(is_array($value) && !empty($fieldName)) {
					$whereNotIn[] =  $fieldName . ' NOT IN (' . implode(',',array_map(array('MySQL', 'escapeValue'), $value)).')';
				} else {
					$whereNotIn[] .= $fieldName . ' = ' .  MySQL::escapeValue($value);
				}
			}
			$where  = implode(' AND ', $whereNotIn);
			$sql = "UPDATE ?f SET deleted = 1 WHERE {$where} AND deleted = 0";

			self::$DB->q($sql, static::$tableName);
		}
		return (self::$DB->affectedRows() > 0);
	}

	/**
	 * Abstractly deletes(that is, sets `deleted` = 1) to the rows matching a certain criteria
	 * This function will also update the `modified_date` and `modified_user`, if present
	 * @static
	 * @uses Common::checkExistColumns()
	 * @param string $field The name of a table field, i.e. 'id', 'p_url' or 'p_title'
	 * @param mixed $value The field value to look for. Can be a simple type of an array. If it's an array, the function
	 * will compare using the SQL 'IN' operator, i.e. `field1` IN (1, 2, 3)
	 * @param int $limit Optional. The number of rows to return. If not specified, the function will return all matched rows
	 * @return bool TRUE if at least one row matched the condition and was deleted. FALSE otherwise
	 */
	static function abstractDeleteByField($field, $value, $limit=0) {
			if (is_array($value) && count($value) > 0) {
				$where = " WHERE ?f IN (" . implode(',',array_map(array('MySQL', 'escapeValue'), $value)) . ")";
			} else {
				$where = " WHERE ?f = " . MySQL::escapeValue($value);
			}
			$attributes = array('deleted' => 1);
			$extendAttributes = array_merge(self::checkExistColumns(), $attributes);
			$resultAttributes = array();
			foreach($extendAttributes as $key => $value) {
				$resultAttributes[] = $key . " = " . MySQL::escapeValue($value);
			}

			$sql = "UPDATE ?f SET " . implode(', ', $resultAttributes) . " {$where} AND deleted = 0";
			if ($limit > 0) {
				$sql .= " LIMIT ".(int)$limit;
			}

			self::$DB->q($sql, static::$tableName, $field, $value);
			return (self::$DB->affectedRows() > 0);
	}

	/**
	 * Abstractly deletes(that is, sets `updated`=1) a record
	 *
	 * @uses Common::checkExistColumns($update)
	 * @param integer $id The primary key value to look for
	 * @return boolean TRUE on success(at least one row was deleted) and FALSE on failure(no row matched the id)
	 */
	static function abstractDelete($id = 0) {
		if (!$id) {
			return false;
		}
		$attributes = array('deleted' => 1);
		$extendAttributes = array_merge(self::checkExistColumns(), $attributes);
		$resultAttributes = array();
		foreach($extendAttributes as $key => $value) {
			$resultAttributes[] = $key . " = " . MySQL::escapeValue($value);
		}

		$sql = 'UPDATE ?f SET ' . implode(', ', $resultAttributes) . ' WHERE ?f=?i;';
		self::$DB->q($sql, static::$tableName, static::$idField, $id);
		return (self::$DB->affectedRows() > 0);
	}

	/**
	 * Lock table
	 */
	public function lockTable() {
		self::$DB->lockTable(static::$tableName);
	}

	/**
	 * Commit
	 */
	public function commit() {
		self::$DB->unlockTables();
	}

	/**
	 * Check if record exists in the database.
	 *
	 * Used for checking if a value already exists in the database.
	 * Ex.: username, Partner ID
	 *
	 * <b>Note</b> If you are passing arrays of fields and values, the count of both arrays must be equal, otherwise the
	 * function will return FALSE. If one of these array is a hash, so must be the other one, moreover, the keys of both
	 * hashes must be the same, or else the function may work incorrectly or show a notice.
	 *
	 * @param mixed $values Column value. Can be a primitive type or an array of primitive types, i.e. array(1, 'developer', 23)
	 * @param array|string $type Table column name. Ca be a string or an array of strings, i.e. array('id', 'pr_name', 'pr_age')
	 * @param string $condition Optional. An SQL logical condition, like 'AND', 'AND NOT', 'OR' etc. This parameter only makes sense
	 * if the previous two are arrays. Defaults to 'AND'
	 * @return boolean TRUE on success and FALSE on failure(the row does not exist OR the parameters are incorrect)
	 */
	public static function checkExists($values, $type='username', $condition='AND') {
		if (is_array($values) && is_string($type) && $condition == 'OR') {
			$sqlAdd = $type.'=';
			$sqlAdd .= implode(' '.$condition.' '.$type.'=', array_map(array('MySQL', 'escapeValue'), $values));
		} else if (is_array($values) && is_array($type)) {
			if (count($values) == count($type)) {
				$sqlAddArray = array();
				foreach ($values as $key => $value) {
					$sqlAddArray[] = $type[$key].'='.MySQL::escapeValue($value);
				}
				$sqlAdd = implode(' '.$condition.' ', $sqlAddArray);
			} else {
				return false;
			}
		} else {
			$sqlAdd = $type.'='.MySQL::escapeValue($values);
		}

		$sql = "SELECT ?f FROM ?f WHERE ({$sqlAdd}) AND `deleted` = 0;";
		self::$DB->q($sql, static::$idField, static::$tableName);

		return (self::$DB->numRows() > 0);
	}

	/**
	 * Fetch result from class table with limit condition
	 *
	 * @static
	 * @uses Common::find(...)
	 * @uses Common::$tableName
	 * @param string $limitSql The `LIMIT` SQL Statement
	 * @return array Object array on success and FALSE on failure
	 */
	public static function getByLimit($limitSql) {
		return self::find("SELECT * FROM ?f WHERE `deleted` = 0 ".$limitSql, static::$tableName);
	}

	/**
	 * Make pagination
	 *
	 * Result is an array with mysql query limit as first value and the pagination array as second value
	 * @deprecated
	 * @param integer $cp Current page number
	 * @param mixed $rpp If is not null must be integer (mean results per page)
	 * @param integer $totalElements In case this number is given no standard sql query will be done
	 * @return array
	 */
	public static function getPagination($cp=1, $rpp=NULL, $totalElements = 0) {

		$return_set = array();
		if ((int)$rpp <= 0) {
			// If the "results per page" variable wasn't set, assign to it global RESULTS_PER_PAGE value
			$rpp = conf('RESULTS_PER_PAGE');
		}
		if ((int)$totalElements == 0) {
			$totalElements = self::$DB->selectCell("SELECT COUNT(*) FROM ?f WHERE `deleted`=0;", static::$tableName);
		}
		$total_pages = ceil($totalElements/$rpp);

		if (!$cp || (int)$cp > $total_pages) // If the current page is not set or it's bigger than total number of pages, assign to it 1
			$cp = 1;

		$return_set[0] = " LIMIT ".(($cp-1)*$rpp).", ".$rpp;  //[1] = limit 0,5 ; 5,5 ; 10,5...

		$return_set[1] = array();

		for ($i=1;$i<=$total_pages;$i++) {					// [2] [1 2 3 4 5 6 ... ] = 1 2 3 4 5 6 ...
			$return_set[1][$i] = $i;
		}

		return $return_set;
	}

	/**
	 * Search all fields by certain criteria, allowing SQL LIKE comparing
	 *
	 * @uses Common::find(...)
	 * @param array|bool $likeArray Array of field names (as array keys) and values to be searched (as array values) using LIKE statement
	 * @param array|bool $preciseArray Array of field names (as array keys) and values to be searched exactly (as array values)
	 * @return mixed Object array on success and FALSE on failure
	 */
	public static function search($likeArray = false, $preciseArray = false) {
		$sql = '
			SELECT	*
			FROM	'.static::$tableName.'
			WHERE	(';
		$fieldCount = 0;
		if ($likeArray) {
			foreach($likeArray as $fieldName=>$fieldValue) {
				if ($fieldCount) {
					$sql .= ' OR ';
				}
				$fieldName = preg_replace("/[^a-z0-9_]/i", "", $fieldName);
				$sql .= " `{$fieldName}` LIKE '%".self::$DB->escapeString($fieldValue)."%' ";
				$fieldCount++;
			}
		}

		if ($preciseArray) {
			foreach($preciseArray as $fieldName=>$fieldValue) {
				if ($fieldCount) {
					$sql .= 'AND ';
				}
				$fieldName = preg_replace("/[^a-z0-9_]/i", "", $fieldName);
				$sql .= " `{$fieldName}` = ".self::$DB->escapeValue($fieldValue)." ";
			}
		}
		$sql .= ") AND `deleted` = 0;";
		return self::find($sql);
	}

    /**
	 * Generates an insert string based on the data you supply, and runs the query.
	 *
	 * @uses MySQL::insertBatch($tableName,$data,$duplicateUpdate)
	 * @param array $data Associative array of values
     * @param bool $duplicateUpdate Update the entry if an existing entry's unique field or combination of fields would
	 * be duplicated by the batch being inserted
     * @return bool TRUE on success and FALSE on failure
     * @example
     * $data = array(
           array(
              'name' => 'My Name 2' ,
              'date' => 'My date 2'
           ),
           array(
              'name' => 'Another Name 2' ,
              'date' => 'Another date 2'
           )
        );
     *
     * @Usage: Db_Test::insertBatch($data [, bool $duplicateUpdate]);
     *
	 */
    public static function insertBatch($data, $duplicateUpdate = false) {
        return self::$DB->insertBatch(static::$tableName, $data, $duplicateUpdate);
    }

    /**
	 * Generates an update string based on the data you supply, and runs the query.
	 *
	 * @uses MySQL::updateBatch($tableName,$data,$idField)
	 * @param array $data The first parameter is an associative array of values
	 * @param string $idField The second parameter is the where key
     * @return bool TRUE on success and FALSE on failure
     * @example
     * $data = array(
           array(
              'id' => '1' ,
              'name' => 'My Name 2' ,
              'date' => 'My date 2'
           ),
           array(
              'id' => '2' ,
              'name' => 'Another Name 2' ,
              'date' => 'Another date 2'
           )
        );
     *
     * @Usage: Db_Test::updateBatch($data [, $whereKey]);
     *          $whereKey can be 'id' or 'title' or 'date'
     *
	 */
    public static function updateBatch($data, $idField = 'id') {
        return self::$DB->updateBatch(static::$tableName, $data, $idField);
    }

    /**
     * Gets all the possible values for field with an enum value
     * @param string $enumColumnName The name of the column(field name)
     * @return array|bool The
     */
    public static function getEnumValues($enumColumnName) {
        if(!empty($enumColumnName)) {
            $enumResult = self::$DB->selectRow('SHOW COLUMNS FROM ?f LIKE ?', static::$tableName, $enumColumnName);
            if($enumResult) {
                preg_match_all("/'(.*)\'/U", $enumResult['Type'], $enumArray);
                return $enumArray[1];
            } else {
                return false;
            }
        } else {
            return false;
        }
    }
    /**
     * Move the entry up based on position field
	 * @uses Common::$positionField
     * @return bool True on success, false on failure
     */
    public function positionMoveUp() {
        if(!$this->positionCommonFields || !static::$positionField) {
            return false;
        }
        $whereFields = array();
        foreach($this->positionCommonFields as $key=>$value) {
            $whereFields[] = $key . '=' . self::$DB->escapeValue($value);
        }
        $whereFields = implode(' AND ', $whereFields);

        $currentPosition = $this->objectArray->{static::$positionField};
        // Find out what row is before current one
        $sql_previous = 'SELECT ?f, ?f FROM ?f WHERE '. $whereFields .'AND ?f < ?i AND `deleted` = 0 ORDER BY '. static::$positionField .' DESC LIMIT 1;';
        $previousRow = self::$DB->selectRow($sql_previous, static::$idField, static::$positionField, static::$tableName, static::$positionField, $currentPosition);

        if($previousRow) {
            $previousId = $previousRow[static::$idField];
            $decrementPosition = $previousRow[static::$positionField];
            $sql = "UPDATE ?f SET ?f=?i WHERE ?f=?i AND `deleted` = 0; ";
            self::$DB->q($sql, static::$tableName, static::$positionField, $currentPosition, static::$idField, $previousId);
            if(self::$DB->affectedRows()) {
                self::$DB->q($sql, static::$tableName, static::$positionField, $decrementPosition, static::$idField, $this->{static::$idField});
                return self::$DB->affectedRows() ? true : false;
            } else {
                return false;
            }
        } else {
            return false;
        }
    }

    /**
	 * Move the entry down based on position field
	 * @uses Common::$positionField
	 * @return bool True on success, false on failure
     */
    public function positionMoveDown() {
        if(!$this->positionCommonFields || !static::$positionField) {
            return false;
        }
        $whereFields = array();
        foreach($this->positionCommonFields as $key=>$value) {
            $whereFields[] = $key . '=' . self::$DB->escapeValue($value);
        }
        $whereFields = implode(' AND ', $whereFields);
        //Current ID
        $currentPosition = $this->objectArray->{static::$positionField};
        // Find out what row is before current one
        $sql_next = 'SELECT ?f, ?f FROM ?f WHERE '. $whereFields .'AND ?f > ?i AND `deleted` = 0 ORDER BY '. static::$positionField .' ASC LIMIT 1;';
        $nextRow = self::$DB->selectRow($sql_next, static::$idField, static::$positionField, static::$tableName, static::$positionField, $currentPosition);

        if($nextRow) {
            $nextId = $nextRow[static::$idField];
            $incrementPosition = $nextRow[static::$positionField];
            $sql = "UPDATE ?f SET ?f = ?i WHERE ?f = ?i AND `deleted` = 0;";
            self::$DB->q($sql, static::$tableName, static::$positionField, $currentPosition, static::$idField, $nextId);
            if(self::$DB->affectedRows()) {
                self::$DB->q($sql, static::$tableName, static::$positionField, $incrementPosition, static::$idField, $this->{static::$idField});
                return self::$DB->affectedRows() ? true : false;
            } else {
                return false;
            }
        } else {
            return false;
        }
    }

	/**
	 * Clean up the entries position based on position field
	 * @uses Common::$positionField
	 * @uses Common::$positionCommonFields
	 * @return bool FALSE if on the required class fields are not valid, true in every other case
	 */
    public function positionCleanUp() {
        if(!$this->positionCommonFields || !static::$positionField) {
            return false;
        }
        $whereFields = array();
        foreach($this->positionCommonFields as $key=>$value) {
            $whereFields[] = $key . '=' . self::$DB->escapeValue($value);
        }
        $whereFields = implode(' AND ', $whereFields);

        $sql = 'SELECT ?f, ?f FROM ?f WHERE '. $whereFields .'AND `deleted` = 0 ORDER BY ?f ASC;';
        $result = self::$DB->q(true, $sql,static::$idField, static::$positionField, static::$tableName, static::$positionField);
        $sql = 'UPDATE ?f SET ?f = ?i WHERE ?f = ?i AND `deleted` = 0;';
        $count = 1;
        while($data = $result->fetch_assoc()) {
            self::$DB->q($sql, static::$tableName,static::$positionField, $count, static::$idField, $data['id']);
            $count = $count +1;
        }
        return true;
    }

	/**
	 * Use for Insert And Edit Row after save row receives last position
	 * @uses Common::$positionField
	 * @uses Common::$positionCommonFields
	 * @return bool FALSE if on the required class fields are not valid, or if the positioning failed
	 */
    public function positionSetLast() {
        if(!$this->positionCommonFields || !static::$positionField) {
            return false;
        }
        $whereFields = array();
        foreach($this->positionCommonFields as $key=>$value) {
            $whereFields[] = $key . '=' . self::$DB->escapeValue($value);
        }
        $whereFields = implode(' AND ', $whereFields);

        $sql = 'SELECT MAX(?f) FROM ?f WHERE '. $whereFields .' AND `deleted` = 0;';
        $nextPosition = self::$DB->selectCell($sql, static::$positionField, static::$tableName) + 1;        
        $sql = 'UPDATE ?f SET ?f = ?i WHERE ?f = ?i';
        if($nextPosition > 1) {
            self::$DB->q($sql, static::$tableName, static::$positionField, $nextPosition, static::$idField, $this->{static::$idField});
            return self::$DB->affectedRows() ? true : false;
        } else {
            self::$DB->q($sql, static::$tableName, static::$positionField, 1, static::$idField, $this->{static::$idField});
            return self::$DB->affectedRows() ? true : false;
        }
    }

	/**
	 * Clones a complete row with id=$id from static::$tableName
	 * Additionally you can pass an associative array with the key = db_field_name and value = the static value which you need
	 *
	 * @static
	 * @param int $id
	 * @param array $optionalFieldValues
	 * @return bool|mixed
	 */
	public static function cloneRow($id = 0, $optionalFieldValues = array()){
		if (!$id) {
			return false;
		}
		$dbFields = static::$dbFields;
		foreach ($dbFields as &$field) {
			if ($field == 'id') {
				$field = 'null';
			} elseif (in_array($field, array_keys($optionalFieldValues))) {
				$field = self::$DB->escapeValue($optionalFieldValues[$field]);
			} else {
				$field = '`' . $field . '`';
			}
		}
		$fields = implode(',', $dbFields);
		if (self::$DB->q("INSERT INTO ?f SELECT {$fields} FROM ?f WHERE `id` = ? ;", static::$tableName, static::$tableName, $id)) {
			$lastId = self::$DB->lastId();
			return $lastId;
		}
		return false;
	}

	/**
	 * Destructor for every inherited class by Common
	 */
	public function __destruct() {
		foreach(array_keys(get_object_vars($this)) as $varName=>$varValue) {
			$this->$varName = null;
		}
	}
}
